﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace KingCat_DataBase
{
    public class DataBaseDao
    {

        private OleDbCommand MyComm;
        private OleDbDataReader MyRead;
        private static DataBaseDao dbd;


        private DataBaseDao()
        {
            MyComm = new OleDbCommand();
            MyComm.Connection = DataBaseConnection.getInstance().getConnection();
            MyComm.CommandType = CommandType.Text;
        }

        public static DataBaseDao getInstance()
        {
            if (dbd == null)
            {
                dbd = new DataBaseDao();
            }
            return dbd;
        }


        /// <summary>
        /// 检查系统用户是否存在
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="userPassword">密码</param>
        /// <returns></returns>
        public string validateOfUser(String userName, String userPassword)
        {
            String sql = "select *from SystemUser where userName='" + userName + "' and userPassword='" + userPassword + "'";
            string msg = "";//签名
            MyComm.CommandText = sql;
            OleDbDataReader MyRead = MyComm.ExecuteReader();
            if (MyRead.Read())
            {
                msg = MyRead.GetString(7);
                MyRead.Close();
            }
            return msg;
           
        }


        /// <summary>
        /// 检查服务器管理员是否存在
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="userPassword">密码</param>
        /// <returns></returns>
        public Boolean validateOfAdminitrator(String userName, String userPassword)
        {
            Boolean flag = false;
            String sql = "select *from Administrator where userName='" + userName + "' and userPassword='" + userPassword + "'";
            MyComm.CommandText = sql;
            MyRead = MyComm.ExecuteReader();
            if (MyRead.Read())
            {
                flag = true;
            }
            MyRead.Close();
            return flag;
        }

        /// <summary>
        /// 获取Tcp设置的Ip地址及端口号
        /// </summary>
        /// <returns></returns>
        public String[] getTcpInfo()
        {
            String[] info = new String[2];
            String sql = "select *from TcpServer";
            MyComm.CommandText = sql;
            MyRead = MyComm.ExecuteReader();
            if (MyRead.Read())
            {
                info[0] = MyRead.GetString(1);
                info[1] = MyRead.GetString(2);
            }
            MyRead.Close();
            return info;
        }

        /// <summary>
        /// 更新Tcp信息
        /// </summary>
        /// <param name="serverIp">服务器ip地址</param>
        /// <param name="serverPort">服务器端口号</param>
        public void UpdataTcpInfo(string serverIp, string serverPort)
        {
            string sql = "update TcpServer set serverIp='" + serverIp + "', serverPort='" + serverPort + "' where id=1";
            MyComm.CommandText = sql;
            MyComm.ExecuteNonQuery();
        }


        /// <summary>
        /// 获得系统中所有的用户
        /// </summary>
        /// <returns></returns>
        public DataTable getAllUser()
        {
            OleDbDataAdapter da = new OleDbDataAdapter("select id ,userName,userPassword,age,sex,trueName,email,constellation,blood from SystemUser", DataBaseConnection.getInstance().getConnection());
            DataTable ds = new DataTable();
            da.Fill(ds);
            return ds;
        }

        /// <summary>
        /// 获得系统用户的个数
        /// </summary>
        /// <returns></returns>
        public int getSystemUserNumber()
        {
            OleDbDataAdapter da = new OleDbDataAdapter("select id ,userName,userPassword,age,sex,trueName,email,constellation,blood from SystemUser", DataBaseConnection.getInstance().getConnection());
            DataTable ds = new DataTable();
            da.Fill(ds);
            int i = ds.Rows.Count;
            return i;
        }


        /// <summary>
        /// 根据用户的id获得用户的信息
        /// </summary>
        /// <param name="userId">用户id号</param>
        /// <returns></returns>
        public DataTable getUserById(String userId)
        {
            OleDbDataAdapter odd = new OleDbDataAdapter("select id ,userName,userPassword,age,sex,trueName,email,constellation,blood from SystemUser where id=" + userId, DataBaseConnection.getInstance().getConnection());
            DataTable dt = new DataTable();
            odd.Fill(dt);
            return dt;
        }

        /// <summary>
        /// 根据用户的id删除用户,返回删除后的所有用户
        /// </summary>
        /// <param name="userId">用户id号</param>
        /// <returns></returns>
        public DataTable deleteUserByUserId(int userId)
        {
            string sql = "delete from SystemUser where id=" + userId;
            MyComm.CommandText = sql;
            MyComm.ExecuteNonQuery();
            OleDbDataAdapter odd = new OleDbDataAdapter("select id ,userName,userPassword,age,sex,trueName,email,constellation,blood from SystemUser", DataBaseConnection.getInstance().getConnection());
            DataTable dt = new DataTable();
            odd.Fill(dt);
            return dt;
        }


        /// <summary>
        /// 根据用户的名称删除用户,返回删除后的所有用户
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns></returns>
        public DataTable deleteUserByUserUserName(String userName)
        {
            string sql = "delete from SystemUser where userName='" + userName + "'";
            MyComm.CommandText = sql;
            MyComm.ExecuteNonQuery();
            OleDbDataAdapter odd = new OleDbDataAdapter("select id ,userName,userPassword,age,sex,trueName,email,constellation,blood from SystemUser", DataBaseConnection.getInstance().getConnection());
            DataTable dt = new DataTable();
            odd.Fill(dt);
            return dt;
        }

        /// <summary>
        ///  添加系统用户
        /// </summary>
        /// <param name="msg"></param>
        public void addSystemUser(String msg)
        {
            string[] us = msg.Split(new char[] { '|' });
            String strsql;
            strsql = "insert into SystemUser(userName,userPassword,age,sex,picture,trueName,constellation,blood,email) values('" + us[1] + "','" + us[2] + "','" + us[3] + "','" + us[4] + "','" + us[5] + "','" + us[6] + "','" + us[7] + "','" + us[8] + "','" + us[9] + "')";
            MyComm.CommandText = strsql;
            MyComm.ExecuteNonQuery();
        }


        /// <summary>
        ///  添加在线用户
        /// </summary>
        /// <param name="msg"></param>
        public void addOlinemUser(String msg)
        {
            string[] us = msg.Split(new char[] { '|' });
            string strsql = "insert into OnlineUser(userName,nickName,Ip,Port,TimeLength) values('" + us[0] + "','" + us[1] + "','" + us[2] + "','" + us[3] + "','"+us[4]+"')";
            MyComm.CommandText = strsql;
            MyComm.ExecuteNonQuery();
        }


        /// <summary>
        /// 根据用户的名称删除用户,返回删除后的所有用户
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns></returns>
        public void deleteOnlieUser(String userName)
        {
            string sql = "delete from OnlineUser where userName='" + userName + "'";
            MyComm.CommandText = sql;
            MyComm.ExecuteNonQuery();
        }


        /// <summary>
        /// 获得所有的在线用户
        /// </summary>
        /// <returns></returns>
        public string getAllOnlineUser()
        {
            string msg="";
            String sql = "select *from OnlineUser";
            MyComm.CommandText = sql;
            MyRead = MyComm.ExecuteReader();
            while(MyRead.Read())
            {
                msg = msg + MyRead.GetString(1);
                msg = msg + "|"+MyRead.GetString(2)+"|";
            }
            msg = msg.Substring(0,msg.LastIndexOf("|"));
            MyRead.Close();
            return msg;
        }

           


    }
}
